{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ⭐ 1- Intro to Data Cleaning and Preparation ⭐\n",
    "In this chapter, you will: \n",
    "\n",
    "•\tExercise 1: Load data into a Spark DataFrame (DF) \n",
    "\n",
    "•\tExercise 2: Query the DF using SQL to get a feel for the data \n",
    "\n",
    "•\tExercise 3: Filter and transform the Data \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder \\\n",
    "    .master('local[*]') \\\n",
    "    .appName(\"Intro\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 1: Load the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.csv ('training_bot_data.csv', header= True) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What is the size of the data? use count() function "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2840"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# understand what is the data size:\n",
    "df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Immutability\n",
    "\n",
    "DataFrame in Spark is **immutable**.\n",
    "\n",
    "What does that mean?\n",
    "It means that every action we do on DataFrame doesn't change the actual DataFrame!\n",
    "\n",
    "Instead, it creates a new DataFrame.\n",
    "Run the next commands and get a feel for working with DataFrame.\n",
    "\n",
    "Don't worry if you don't understand everything completely, the next exercises go deeper into it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>url</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>default_profile_image</th>\n",
       "      <th>has_extended_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.16E+17</td>\n",
       "      <td>\"\"\"815745789754417152\"\"\"</td>\n",
       "      <td>\"\"\"HoustonPokeMap\"\"\"</td>\n",
       "      <td>\"\"\"Houston</td>\n",
       "      <td>TX\"\"\"</td>\n",
       "      <td>\"\"\"Rare and strong PokŽmon in Houston</td>\n",
       "      <td>TX. See more PokŽmon at https://t.co/dnWuDbFR...</td>\n",
       "      <td>\"\"\"https://t.co/dnWuDbFRkt\"\"\"</td>\n",
       "      <td>1291</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>\"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>78554</td>\n",
       "      <td>\"\"\"en\"\"\"</td>\n",
       "      <td>\"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...</td>\n",
       "      <td>\"\"id\"\": 840951532543737900</td>\n",
       "      <td>\"\"id_str\"\": \"\"840951532543737856\"\"</td>\n",
       "      <td>\"\"text\"\": \"\"[Southeast Houston] Chansey ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4843621225</td>\n",
       "      <td>4843621225</td>\n",
       "      <td>kernyeahx</td>\n",
       "      <td>Templeville town, MD, USA</td>\n",
       "      <td>From late 2014 Socium Marketplace will make sh...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>349</td>\n",
       "      <td>0</td>\n",
       "      <td>2/1/2016 7:37</td>\n",
       "      <td>38</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>31</td>\n",
       "      <td>en</td>\n",
       "      <td>null</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>Keri Nelson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                    id_str           screen_name  \\\n",
       "0    8.16E+17  \"\"\"815745789754417152\"\"\"  \"\"\"HoustonPokeMap\"\"\"   \n",
       "1  4843621225                4843621225             kernyeahx   \n",
       "\n",
       "                    location  \\\n",
       "0                 \"\"\"Houston   \n",
       "1  Templeville town, MD, USA   \n",
       "\n",
       "                                         description  \\\n",
       "0                                              TX\"\"\"   \n",
       "1  From late 2014 Socium Marketplace will make sh...   \n",
       "\n",
       "                                     url  \\\n",
       "0  \"\"\"Rare and strong PokŽmon in Houston   \n",
       "1                                   None   \n",
       "\n",
       "                                     followers_count  \\\n",
       "0   TX. See more PokŽmon at https://t.co/dnWuDbFR...   \n",
       "1                                                  1   \n",
       "\n",
       "                   friends_count listed_count     created_at favourites_count  \\\n",
       "0  \"\"\"https://t.co/dnWuDbFRkt\"\"\"         1291              0               10   \n",
       "1                            349            0  2/1/2016 7:37               38   \n",
       "\n",
       "                               verified statuses_count   lang status  \\\n",
       "0  \"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"              0  FALSE  78554   \n",
       "1                                 FALSE             31     en   null   \n",
       "\n",
       "  default_profile                              default_profile_image  \\\n",
       "0        \"\"\"en\"\"\"  \"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...   \n",
       "1            TRUE                                              FALSE   \n",
       "\n",
       "               has_extended_profile                                      name  \\\n",
       "0        \"\"id\"\": 840951532543737900        \"\"id_str\"\": \"\"840951532543737856\"\"   \n",
       "1                             FALSE                               Keri Nelson   \n",
       "\n",
       "                                                 bot  \n",
       "0        \"\"text\"\": \"\"[Southeast Houston] Chansey ...  \n",
       "1                                                  1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.limit(2) .toPandas ()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = df.select('bot')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>\"\"text\"\": \"\"[Southeast Houston] Chansey ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 bot\n",
       "0        \"\"text\"\": \"\"[Southeast Houston] Chansey ...\n",
       "1                                                  1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new.limit(2) .toPandas ()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You probably notice that `df_new`, and `df` are different!\n",
    "They are pointers to two different DataFrames.\n",
    "\n",
    "Try the next command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>\"\"text\"\": \"\"[Southeast Houston] Chansey ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 bot\n",
       "0        \"\"text\"\": \"\"[Southeast Houston] Chansey ...\n",
       "1                                                  1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select('bot').limit(2) .toPandas ()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>url</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>default_profile_image</th>\n",
       "      <th>has_extended_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.16E+17</td>\n",
       "      <td>\"\"\"815745789754417152\"\"\"</td>\n",
       "      <td>\"\"\"HoustonPokeMap\"\"\"</td>\n",
       "      <td>\"\"\"Houston</td>\n",
       "      <td>TX\"\"\"</td>\n",
       "      <td>\"\"\"Rare and strong PokŽmon in Houston</td>\n",
       "      <td>TX. See more PokŽmon at https://t.co/dnWuDbFR...</td>\n",
       "      <td>\"\"\"https://t.co/dnWuDbFRkt\"\"\"</td>\n",
       "      <td>1291</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>\"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>78554</td>\n",
       "      <td>\"\"\"en\"\"\"</td>\n",
       "      <td>\"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...</td>\n",
       "      <td>\"\"id\"\": 840951532543737900</td>\n",
       "      <td>\"\"id_str\"\": \"\"840951532543737856\"\"</td>\n",
       "      <td>\"\"text\"\": \"\"[Southeast Houston] Chansey ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4843621225</td>\n",
       "      <td>4843621225</td>\n",
       "      <td>kernyeahx</td>\n",
       "      <td>Templeville town, MD, USA</td>\n",
       "      <td>From late 2014 Socium Marketplace will make sh...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>349</td>\n",
       "      <td>0</td>\n",
       "      <td>2/1/2016 7:37</td>\n",
       "      <td>38</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>31</td>\n",
       "      <td>en</td>\n",
       "      <td>null</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>Keri Nelson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                    id_str           screen_name  \\\n",
       "0    8.16E+17  \"\"\"815745789754417152\"\"\"  \"\"\"HoustonPokeMap\"\"\"   \n",
       "1  4843621225                4843621225             kernyeahx   \n",
       "\n",
       "                    location  \\\n",
       "0                 \"\"\"Houston   \n",
       "1  Templeville town, MD, USA   \n",
       "\n",
       "                                         description  \\\n",
       "0                                              TX\"\"\"   \n",
       "1  From late 2014 Socium Marketplace will make sh...   \n",
       "\n",
       "                                     url  \\\n",
       "0  \"\"\"Rare and strong PokŽmon in Houston   \n",
       "1                                   None   \n",
       "\n",
       "                                     followers_count  \\\n",
       "0   TX. See more PokŽmon at https://t.co/dnWuDbFR...   \n",
       "1                                                  1   \n",
       "\n",
       "                   friends_count listed_count     created_at favourites_count  \\\n",
       "0  \"\"\"https://t.co/dnWuDbFRkt\"\"\"         1291              0               10   \n",
       "1                            349            0  2/1/2016 7:37               38   \n",
       "\n",
       "                               verified statuses_count   lang status  \\\n",
       "0  \"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"              0  FALSE  78554   \n",
       "1                                 FALSE             31     en   null   \n",
       "\n",
       "  default_profile                              default_profile_image  \\\n",
       "0        \"\"\"en\"\"\"  \"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...   \n",
       "1            TRUE                                              FALSE   \n",
       "\n",
       "               has_extended_profile                                      name  \\\n",
       "0        \"\"id\"\": 840951532543737900        \"\"id_str\"\": \"\"840951532543737856\"\"   \n",
       "1                             FALSE                               Keri Nelson   \n",
       "\n",
       "                                                 bot  \n",
       "0        \"\"text\"\": \"\"[Southeast Houston] Chansey ...  \n",
       "1                                                  1  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.limit(2) .toPandas ()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last `toPandas ()` commands printed different results, \n",
    "\n",
    "### why?\n",
    "\n",
    "`df.select('bot')` functionality returns pointer to a new immutable DataFrame! AHA!\n",
    "\n",
    "Let's have `df_new` and `df` point to the same DataFrame.\n",
    "By doing this, we release the pointer from `df_new` and it can be erased from memory.\n",
    "\n",
    "If we wish to have access to it again, we will need to rerun the logic.\n",
    "Bare that in mind for working with `Apache Spark`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>url</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>default_profile_image</th>\n",
       "      <th>has_extended_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.16E+17</td>\n",
       "      <td>\"\"\"815745789754417152\"\"\"</td>\n",
       "      <td>\"\"\"HoustonPokeMap\"\"\"</td>\n",
       "      <td>\"\"\"Houston</td>\n",
       "      <td>TX\"\"\"</td>\n",
       "      <td>\"\"\"Rare and strong PokŽmon in Houston</td>\n",
       "      <td>TX. See more PokŽmon at https://t.co/dnWuDbFR...</td>\n",
       "      <td>\"\"\"https://t.co/dnWuDbFRkt\"\"\"</td>\n",
       "      <td>1291</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>\"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>78554</td>\n",
       "      <td>\"\"\"en\"\"\"</td>\n",
       "      <td>\"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...</td>\n",
       "      <td>\"\"id\"\": 840951532543737900</td>\n",
       "      <td>\"\"id_str\"\": \"\"840951532543737856\"\"</td>\n",
       "      <td>\"\"text\"\": \"\"[Southeast Houston] Chansey ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4843621225</td>\n",
       "      <td>4843621225</td>\n",
       "      <td>kernyeahx</td>\n",
       "      <td>Templeville town, MD, USA</td>\n",
       "      <td>From late 2014 Socium Marketplace will make sh...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>349</td>\n",
       "      <td>0</td>\n",
       "      <td>2/1/2016 7:37</td>\n",
       "      <td>38</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>31</td>\n",
       "      <td>en</td>\n",
       "      <td>null</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>Keri Nelson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                    id_str           screen_name  \\\n",
       "0    8.16E+17  \"\"\"815745789754417152\"\"\"  \"\"\"HoustonPokeMap\"\"\"   \n",
       "1  4843621225                4843621225             kernyeahx   \n",
       "\n",
       "                    location  \\\n",
       "0                 \"\"\"Houston   \n",
       "1  Templeville town, MD, USA   \n",
       "\n",
       "                                         description  \\\n",
       "0                                              TX\"\"\"   \n",
       "1  From late 2014 Socium Marketplace will make sh...   \n",
       "\n",
       "                                     url  \\\n",
       "0  \"\"\"Rare and strong PokŽmon in Houston   \n",
       "1                                   None   \n",
       "\n",
       "                                     followers_count  \\\n",
       "0   TX. See more PokŽmon at https://t.co/dnWuDbFR...   \n",
       "1                                                  1   \n",
       "\n",
       "                   friends_count listed_count     created_at favourites_count  \\\n",
       "0  \"\"\"https://t.co/dnWuDbFRkt\"\"\"         1291              0               10   \n",
       "1                            349            0  2/1/2016 7:37               38   \n",
       "\n",
       "                               verified statuses_count   lang status  \\\n",
       "0  \"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"              0  FALSE  78554   \n",
       "1                                 FALSE             31     en   null   \n",
       "\n",
       "  default_profile                              default_profile_image  \\\n",
       "0        \"\"\"en\"\"\"  \"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...   \n",
       "1            TRUE                                              FALSE   \n",
       "\n",
       "               has_extended_profile                                      name  \\\n",
       "0        \"\"id\"\": 840951532543737900        \"\"id_str\"\": \"\"840951532543737856\"\"   \n",
       "1                             FALSE                               Keri Nelson   \n",
       "\n",
       "                                                 bot  \n",
       "0        \"\"text\"\": \"\"[Southeast Houston] Chansey ...  \n",
       "1                                                  1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new = df\n",
    "df_new.limit(2) .toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By the Way! `limit(2)` returns a pointer to a DataFrame with 2 rows.\n",
    "\n",
    "Interesting! This is what **Immutability** means!! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Exercise 2: Get a feel for the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at 2 records from the DataFrame to understand the values better before filter: use take() function\n",
    "\n",
    "```python\n",
    "df.take(insert an integer here)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(id='8.16E+17', id_str='\"\"\"815745789754417152\"\"\"', screen_name='\"\"\"HoustonPokeMap\"\"\"', location='\"\"\"Houston', description=' TX\"\"\"', url='\"\"\"Rare and strong PokŽmon in Houston', followers_count=' TX. See more PokŽmon at https://t.co/dnWuDbFRkt\"\"\"', friends_count='\"\"\"https://t.co/dnWuDbFRkt\"\"\"', listed_count='1291', created_at='0', favourites_count='10', verified='\"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"', statuses_count='0', lang='FALSE', status='78554', default_profile='\"\"\"en\"\"\"', default_profile_image='\"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 +0000 2017\"\"', has_extended_profile='      \"\"id\"\": 840951532543737900', name='      \"\"id_str\"\": \"\"840951532543737856\"\"', bot='      \"\"text\"\": \"\"[Southeast Houston] Chansey (F) (IV: 73%) until 11:11:37AM at 2511 Winbern St https://t.co/HYRIyq4mF7 https://t.co/bydOOKsEEI\"\"'),\n",
       " Row(id='4843621225', id_str='4843621225', screen_name='kernyeahx', location='Templeville town, MD, USA', description='From late 2014 Socium Marketplace will make shopping for fundamental business services more simple, more cost effective and more about you.', url=None, followers_count='1', friends_count='349', listed_count='0', created_at='2/1/2016 7:37', favourites_count='38', verified='FALSE', statuses_count='31', lang='en', status='null', default_profile='TRUE', default_profile_image='FALSE', has_extended_profile='FALSE', name='Keri Nelson', bot='1')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.take(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check out the schema stracture of the DataFrame.\n",
    "\n",
    "What are the values types?\n",
    "Use:\n",
    "\n",
    "```python\n",
    "df.printSchema()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: string (nullable = true)\n",
      " |-- id_str: string (nullable = true)\n",
      " |-- screen_name: string (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- description: string (nullable = true)\n",
      " |-- url: string (nullable = true)\n",
      " |-- followers_count: string (nullable = true)\n",
      " |-- friends_count: string (nullable = true)\n",
      " |-- listed_count: string (nullable = true)\n",
      " |-- created_at: string (nullable = true)\n",
      " |-- favourites_count: string (nullable = true)\n",
      " |-- verified: string (nullable = true)\n",
      " |-- statuses_count: string (nullable = true)\n",
      " |-- lang: string (nullable = true)\n",
      " |-- status: string (nullable = true)\n",
      " |-- default_profile: string (nullable = true)\n",
      " |-- default_profile_image: string (nullable = true)\n",
      " |-- has_extended_profile: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- bot: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the next function:\n",
    "\n",
    "```python\n",
    "df.limit(5) .toPandas ()\n",
    "```\n",
    "\n",
    "What happened here? `toPandas` function took the Spark DataFrame and translated it into Pandas DataFrame.\n",
    "\n",
    "#### Run this function only on small data sets and when exploring the data. \n",
    "#### Otherwise, you might run out of memory! \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>url</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>default_profile_image</th>\n",
       "      <th>has_extended_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.16E+17</td>\n",
       "      <td>\"\"\"815745789754417152\"\"\"</td>\n",
       "      <td>\"\"\"HoustonPokeMap\"\"\"</td>\n",
       "      <td>\"\"\"Houston</td>\n",
       "      <td>TX\"\"\"</td>\n",
       "      <td>\"\"\"Rare and strong PokŽmon in Houston</td>\n",
       "      <td>TX. See more PokŽmon at https://t.co/dnWuDbFR...</td>\n",
       "      <td>\"\"\"https://t.co/dnWuDbFRkt\"\"\"</td>\n",
       "      <td>1291</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>\"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>78554</td>\n",
       "      <td>\"\"\"en\"\"\"</td>\n",
       "      <td>\"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...</td>\n",
       "      <td>\"\"id\"\": 840951532543737900</td>\n",
       "      <td>\"\"id_str\"\": \"\"840951532543737856\"\"</td>\n",
       "      <td>\"\"text\"\": \"\"[Southeast Houston] Chansey ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4843621225</td>\n",
       "      <td>4843621225</td>\n",
       "      <td>kernyeahx</td>\n",
       "      <td>Templeville town, MD, USA</td>\n",
       "      <td>From late 2014 Socium Marketplace will make sh...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>349</td>\n",
       "      <td>0</td>\n",
       "      <td>2/1/2016 7:37</td>\n",
       "      <td>38</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>31</td>\n",
       "      <td>en</td>\n",
       "      <td>null</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>Keri Nelson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4303727112</td>\n",
       "      <td>4303727112</td>\n",
       "      <td>mattlieberisbot</td>\n",
       "      <td>None</td>\n",
       "      <td>Inspired by the smart, funny folks at @replyal...</td>\n",
       "      <td>https://t.co/P1e1o0m4KC</td>\n",
       "      <td>1086</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>Fri Nov 20 18:53:22 +0000 2015</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>713</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{'retweeted': False, 'is_quote_status': False...</td>\n",
       "      <td>'truncated': False</td>\n",
       "      <td>'in_reply_to_user_id': None</td>\n",
       "      <td>'created_at': 'Mon Mar 13 16:00:00 +0000 2017'</td>\n",
       "      <td>'contributors': None</td>\n",
       "      <td>'in_reply_to_status_id_str': None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3063139353</td>\n",
       "      <td>3063139353</td>\n",
       "      <td>sc_papers</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>2/25/2015 20:11</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>676</td>\n",
       "      <td>en</td>\n",
       "      <td>Construction of human anti-tetanus single-chai...</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>single cell papers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2955142070</td>\n",
       "      <td>2955142070</td>\n",
       "      <td>lucarivera16</td>\n",
       "      <td>Dublin, United States</td>\n",
       "      <td>Inspiring cooks everywhere since 1956.</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>745</td>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2015 17:44</td>\n",
       "      <td>146</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>185</td>\n",
       "      <td>en</td>\n",
       "      <td>null</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>lucarivera16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8.41E+17</td>\n",
       "      <td>8.41E+17</td>\n",
       "      <td>dantheimprover</td>\n",
       "      <td>Austin, TX</td>\n",
       "      <td>Just a guy trying to do good by telling everyo...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>186</td>\n",
       "      <td>0</td>\n",
       "      <td>13/03/2017 22:53</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>11</td>\n",
       "      <td>en</td>\n",
       "      <td>\"Status(_api=&lt;tweepy.api.API object at 0x10192...</td>\n",
       "      <td>'in_reply_to_status_id': None</td>\n",
       "      <td>'in_reply_to_status_id_str': None</td>\n",
       "      <td>'in_reply_to_user_id': None</td>\n",
       "      <td>'in_reply_to_user_id_str': None</td>\n",
       "      <td>'in_reply_to_screen_name': None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2482834658</td>\n",
       "      <td>2482834658</td>\n",
       "      <td>_all_of_us_</td>\n",
       "      <td>in a machine.</td>\n",
       "      <td>bot by @rubicon</td>\n",
       "      <td>None</td>\n",
       "      <td>193</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>Wed May 07 22:29:25 +0000 2014</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>6068</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3333573622</td>\n",
       "      <td>3333573622</td>\n",
       "      <td>KatamariItems</td>\n",
       "      <td>None</td>\n",
       "      <td>[Bot rolled up by @BeachEpisode] Cataloguing e...</td>\n",
       "      <td>None</td>\n",
       "      <td>8227</td>\n",
       "      <td>2</td>\n",
       "      <td>89</td>\n",
       "      <td>Thu Jun 18 22:07:31 +0000 2015</td>\n",
       "      <td>26</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>2597</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2996105102</td>\n",
       "      <td>2996105102</td>\n",
       "      <td>AutophagyPapers</td>\n",
       "      <td>None</td>\n",
       "      <td>Twitterbot for #Autophagy papers. Curated by @...</td>\n",
       "      <td>None</td>\n",
       "      <td>275</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>1/25/2015 17:34</td>\n",
       "      <td>23</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>9922</td>\n",
       "      <td>en</td>\n",
       "      <td>Feeding Schedule And Proteolysis Regulate Auto...</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>Autophagy Papers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3271095818</td>\n",
       "      <td>3271095818</td>\n",
       "      <td>HSC_papers</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>51</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>7/7/2015 15:23</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>2515</td>\n",
       "      <td>en</td>\n",
       "      <td>Functional Selectivity in Cytokine Signaling R...</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>Hematopoiesis</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2717905134</td>\n",
       "      <td>2717905134</td>\n",
       "      <td>everycheese</td>\n",
       "      <td>cheese land</td>\n",
       "      <td>cheese cheese cheese // updates every 4 hours ...</td>\n",
       "      <td>https://t.co/0cV8vQsQSV</td>\n",
       "      <td>51</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>Fri Aug 08 20:23:08 +0000 2014</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>111</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2826073429</td>\n",
       "      <td>2826073429</td>\n",
       "      <td>gyr_papers</td>\n",
       "      <td>None</td>\n",
       "      <td>Literature bot searching arXiv, biorXiv, PeerJ...</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>9/22/2014 9:46</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>230</td>\n",
       "      <td>en</td>\n",
       "      <td>ArXiv: Large-scale chromosome folding versus g...</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>gyrpapers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8.10E+17</td>\n",
       "      <td>8.10E+17</td>\n",
       "      <td>lavon_court</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun Mar 12 01:07:25 +0000 2017</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>None</td>\n",
       "      <td>Lavon Court</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1027583707</td>\n",
       "      <td>1027583707</td>\n",
       "      <td>AjstyleAlen</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>206</td>\n",
       "      <td>0</td>\n",
       "      <td>Sat Dec 22 02:18:49 +0000 2012</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>None</td>\n",
       "      <td>Alen Alex</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>394991274</td>\n",
       "      <td>394991274</td>\n",
       "      <td>Rupesh93505</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun Mar 12 01:06:00 +0000 2017</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>0</td>\n",
       "      <td>en</td>\n",
       "      <td>None</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>None</td>\n",
       "      <td>Rajesh prajapati</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3032363298</td>\n",
       "      <td>3032363298</td>\n",
       "      <td>hard_to_yelp</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>109</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>Fri Feb 20 08:23:00 +0000 2015</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>16067</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2568851552</td>\n",
       "      <td>2568851552</td>\n",
       "      <td>fuckeverywordza</td>\n",
       "      <td>None</td>\n",
       "      <td>Why wait until 2020 for the Z's? Fucking every...</td>\n",
       "      <td>None</td>\n",
       "      <td>250</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>Sun Jun 15 11:20:31 +0000 2014</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>31721</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2954704090</td>\n",
       "      <td>2954704090</td>\n",
       "      <td>yeahTonyaGonzal</td>\n",
       "      <td>Pensacola, USA</td>\n",
       "      <td>Musician, vegan, animal rights activist.</td>\n",
       "      <td>None</td>\n",
       "      <td>15</td>\n",
       "      <td>1941</td>\n",
       "      <td>1</td>\n",
       "      <td>1/1/2015 11:47</td>\n",
       "      <td>319</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>406</td>\n",
       "      <td>en</td>\n",
       "      <td>null</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>yeahTonyaGonzal</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>8.39E+17</td>\n",
       "      <td>8.39E+17</td>\n",
       "      <td>BbbbggU</td>\n",
       "      <td>_._______________, _________</td>\n",
       "      <td>_______ XXX</td>\n",
       "      <td>None</td>\n",
       "      <td>190</td>\n",
       "      <td>1899</td>\n",
       "      <td>0</td>\n",
       "      <td>3/7/2017 22:08</td>\n",
       "      <td>27</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>0</td>\n",
       "      <td>th</td>\n",
       "      <td>None</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>TRUE</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>xXx</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2835723644</td>\n",
       "      <td>2835723644</td>\n",
       "      <td>ecin_gnihtaseod</td>\n",
       "      <td>None</td>\n",
       "      <td>straight-up rip-off of @doesathing_nice, excep...</td>\n",
       "      <td>https://t.co/iEclQxz694</td>\n",
       "      <td>181</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>Tue Sep 30 04:56:09 +0000 2014</td>\n",
       "      <td>0</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>21506</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': [{u'id': 2447379738</td>\n",
       "      <td>u'indices': [0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1918202534</td>\n",
       "      <td>1918202534</td>\n",
       "      <td>tofu_product</td>\n",
       "      <td>None</td>\n",
       "      <td>Follow me to chat. // Reflecting your personal...</td>\n",
       "      <td>None</td>\n",
       "      <td>10175</td>\n",
       "      <td>11465</td>\n",
       "      <td>199</td>\n",
       "      <td>Sun Sep 29 21:35:03 +0000 2013</td>\n",
       "      <td>328</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>65022</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': [{u'id': 85789543</td>\n",
       "      <td>u'indices': [0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2475875424</td>\n",
       "      <td>2475875424</td>\n",
       "      <td>onetruewiseman</td>\n",
       "      <td>AMERICA</td>\n",
       "      <td>The Truest Wisdom (aka trying to make even les...</td>\n",
       "      <td>None</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>Sat May 03 19:04:37 +0000 2014</td>\n",
       "      <td>4</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>6230</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'quoted_status_id': 765179912265146368</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1909219404</td>\n",
       "      <td>1909219404</td>\n",
       "      <td>everycolorbot</td>\n",
       "      <td>the RGB color space</td>\n",
       "      <td>colors.  all of 'em. | developed by @vogon; fe...</td>\n",
       "      <td>https://t.co/ICFGFTYbjQ</td>\n",
       "      <td>106126</td>\n",
       "      <td>0</td>\n",
       "      <td>999</td>\n",
       "      <td>Thu Sep 26 21:22:10 +0000 2013</td>\n",
       "      <td>2</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>30156</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>u'retweeted': False</td>\n",
       "      <td>u'coordinates': None</td>\n",
       "      <td>u'entities': {u'symbols': []</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>4040207472</td>\n",
       "      <td>4040207472</td>\n",
       "      <td>himawari8bot</td>\n",
       "      <td>Space</td>\n",
       "      <td>Unofficial; imagery courtesy: Japan Meteorolog...</td>\n",
       "      <td>https://t.co/OSHkLQWgKQ</td>\n",
       "      <td>2302</td>\n",
       "      <td>3</td>\n",
       "      <td>161</td>\n",
       "      <td>Tue Oct 27 23:06:22 +0000 2015</td>\n",
       "      <td>4</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>7640</td>\n",
       "      <td>en</td>\n",
       "      <td>\"{'favorited': False, 'id_str': '8411371549149...</td>\n",
       "      <td>jerks.&lt;/a&gt;'</td>\n",
       "      <td>'geo': None</td>\n",
       "      <td>'possibly_sensitive': False</td>\n",
       "      <td>'entities': {'urls': []</td>\n",
       "      <td>'media': [{'indices': [20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>3369547257</td>\n",
       "      <td>3369547257</td>\n",
       "      <td>emoji__polls</td>\n",
       "      <td>‰÷•ü</td>\n",
       "      <td>A silly bot by @fourtonfish @botwikidotorg. Im...</td>\n",
       "      <td>https://t.co/OqsKPUkiXl</td>\n",
       "      <td>443</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>7/10/2015 18:30</td>\n",
       "      <td>6</td>\n",
       "      <td>FALSE</td>\n",
       "      <td>9584</td>\n",
       "      <td>en</td>\n",
       "      <td>\"Status(in_reply_to_user_id=None, place=None, ...</td>\n",
       "      <td>'in_reply_to_status_id': None</td>\n",
       "      <td>'text': '_ÙÔö _Ù÷À _Ù_ _ÙÁ #emoji #poll'</td>\n",
       "      <td>'coordinates': None</td>\n",
       "      <td>'in_reply_to_user_id': None}</td>\n",
       "      <td>is_quote_status=False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            id                    id_str           screen_name  \\\n",
       "0     8.16E+17  \"\"\"815745789754417152\"\"\"  \"\"\"HoustonPokeMap\"\"\"   \n",
       "1   4843621225                4843621225             kernyeahx   \n",
       "2   4303727112                4303727112       mattlieberisbot   \n",
       "3   3063139353                3063139353             sc_papers   \n",
       "4   2955142070                2955142070          lucarivera16   \n",
       "5     8.41E+17                  8.41E+17        dantheimprover   \n",
       "6   2482834658                2482834658           _all_of_us_   \n",
       "7   3333573622                3333573622         KatamariItems   \n",
       "8   2996105102                2996105102       AutophagyPapers   \n",
       "9   3271095818                3271095818            HSC_papers   \n",
       "10  2717905134                2717905134           everycheese   \n",
       "11  2826073429                2826073429            gyr_papers   \n",
       "12    8.10E+17                  8.10E+17           lavon_court   \n",
       "13  1027583707                1027583707           AjstyleAlen   \n",
       "14   394991274                 394991274           Rupesh93505   \n",
       "15  3032363298                3032363298          hard_to_yelp   \n",
       "16  2568851552                2568851552       fuckeverywordza   \n",
       "17  2954704090                2954704090       yeahTonyaGonzal   \n",
       "18    8.39E+17                  8.39E+17               BbbbggU   \n",
       "19  2835723644                2835723644       ecin_gnihtaseod   \n",
       "20  1918202534                1918202534          tofu_product   \n",
       "21  2475875424                2475875424        onetruewiseman   \n",
       "22  1909219404                1909219404         everycolorbot   \n",
       "23  4040207472                4040207472          himawari8bot   \n",
       "24  3369547257                3369547257          emoji__polls   \n",
       "\n",
       "                        location  \\\n",
       "0                     \"\"\"Houston   \n",
       "1      Templeville town, MD, USA   \n",
       "2                           None   \n",
       "3                           None   \n",
       "4          Dublin, United States   \n",
       "5                     Austin, TX   \n",
       "6                  in a machine.   \n",
       "7                           None   \n",
       "8                           None   \n",
       "9                           None   \n",
       "10                   cheese land   \n",
       "11                          None   \n",
       "12                          None   \n",
       "13                          None   \n",
       "14                          None   \n",
       "15                          None   \n",
       "16                          None   \n",
       "17                Pensacola, USA   \n",
       "18  _._______________, _________   \n",
       "19                          None   \n",
       "20                          None   \n",
       "21                       AMERICA   \n",
       "22           the RGB color space   \n",
       "23                         Space   \n",
       "24                        ‰÷•ü   \n",
       "\n",
       "                                          description  \\\n",
       "0                                               TX\"\"\"   \n",
       "1   From late 2014 Socium Marketplace will make sh...   \n",
       "2   Inspired by the smart, funny folks at @replyal...   \n",
       "3                                                None   \n",
       "4              Inspiring cooks everywhere since 1956.   \n",
       "5   Just a guy trying to do good by telling everyo...   \n",
       "6                                     bot by @rubicon   \n",
       "7   [Bot rolled up by @BeachEpisode] Cataloguing e...   \n",
       "8   Twitterbot for #Autophagy papers. Curated by @...   \n",
       "9                                                None   \n",
       "10  cheese cheese cheese // updates every 4 hours ...   \n",
       "11  Literature bot searching arXiv, biorXiv, PeerJ...   \n",
       "12                                               None   \n",
       "13                                               None   \n",
       "14                                               None   \n",
       "15                                               None   \n",
       "16  Why wait until 2020 for the Z's? Fucking every...   \n",
       "17           Musician, vegan, animal rights activist.   \n",
       "18                                        _______ XXX   \n",
       "19  straight-up rip-off of @doesathing_nice, excep...   \n",
       "20  Follow me to chat. // Reflecting your personal...   \n",
       "21  The Truest Wisdom (aka trying to make even les...   \n",
       "22  colors.  all of 'em. | developed by @vogon; fe...   \n",
       "23  Unofficial; imagery courtesy: Japan Meteorolog...   \n",
       "24  A silly bot by @fourtonfish @botwikidotorg. Im...   \n",
       "\n",
       "                                      url  \\\n",
       "0   \"\"\"Rare and strong PokŽmon in Houston   \n",
       "1                                    None   \n",
       "2                 https://t.co/P1e1o0m4KC   \n",
       "3                                    None   \n",
       "4                                    None   \n",
       "5                                    None   \n",
       "6                                    None   \n",
       "7                                    None   \n",
       "8                                    None   \n",
       "9                                    None   \n",
       "10                https://t.co/0cV8vQsQSV   \n",
       "11                                   None   \n",
       "12                                   None   \n",
       "13                                   None   \n",
       "14                                   None   \n",
       "15                                   None   \n",
       "16                                   None   \n",
       "17                                   None   \n",
       "18                                   None   \n",
       "19                https://t.co/iEclQxz694   \n",
       "20                                   None   \n",
       "21                                   None   \n",
       "22                https://t.co/ICFGFTYbjQ   \n",
       "23                https://t.co/OSHkLQWgKQ   \n",
       "24                https://t.co/OqsKPUkiXl   \n",
       "\n",
       "                                      followers_count  \\\n",
       "0    TX. See more PokŽmon at https://t.co/dnWuDbFR...   \n",
       "1                                                   1   \n",
       "2                                                1086   \n",
       "3                                                  33   \n",
       "4                                                  11   \n",
       "5                                                   1   \n",
       "6                                                 193   \n",
       "7                                                8227   \n",
       "8                                                 275   \n",
       "9                                                  51   \n",
       "10                                                 51   \n",
       "11                                                  2   \n",
       "12                                                  0   \n",
       "13                                                  1   \n",
       "14                                                  0   \n",
       "15                                                109   \n",
       "16                                                250   \n",
       "17                                                 15   \n",
       "18                                                190   \n",
       "19                                                181   \n",
       "20                                              10175   \n",
       "21                                                 23   \n",
       "22                                             106126   \n",
       "23                                               2302   \n",
       "24                                                443   \n",
       "\n",
       "                    friends_count listed_count  \\\n",
       "0   \"\"\"https://t.co/dnWuDbFRkt\"\"\"         1291   \n",
       "1                             349            0   \n",
       "2                               0           14   \n",
       "3                               0            8   \n",
       "4                             745            0   \n",
       "5                             186            0   \n",
       "6                               0           19   \n",
       "7                               2           89   \n",
       "8                               0           17   \n",
       "9                               3            9   \n",
       "10                              1           12   \n",
       "11                              1            4   \n",
       "12                             29            0   \n",
       "13                            206            0   \n",
       "14                             38            0   \n",
       "15                              0           16   \n",
       "16                              0           25   \n",
       "17                           1941            1   \n",
       "18                           1899            0   \n",
       "19                              0           24   \n",
       "20                          11465          199   \n",
       "21                              4            8   \n",
       "22                              0          999   \n",
       "23                              3          161   \n",
       "24                              2           17   \n",
       "\n",
       "                        created_at favourites_count  \\\n",
       "0                                0               10   \n",
       "1                    2/1/2016 7:37               38   \n",
       "2   Fri Nov 20 18:53:22 +0000 2015                0   \n",
       "3                  2/25/2015 20:11                0   \n",
       "4                   1/1/2015 17:44              146   \n",
       "5                 13/03/2017 22:53                0   \n",
       "6   Wed May 07 22:29:25 +0000 2014                0   \n",
       "7   Thu Jun 18 22:07:31 +0000 2015               26   \n",
       "8                  1/25/2015 17:34               23   \n",
       "9                   7/7/2015 15:23                0   \n",
       "10  Fri Aug 08 20:23:08 +0000 2014                0   \n",
       "11                  9/22/2014 9:46                0   \n",
       "12  Sun Mar 12 01:07:25 +0000 2017                0   \n",
       "13  Sat Dec 22 02:18:49 +0000 2012                0   \n",
       "14  Sun Mar 12 01:06:00 +0000 2017                0   \n",
       "15  Fri Feb 20 08:23:00 +0000 2015                0   \n",
       "16  Sun Jun 15 11:20:31 +0000 2014                0   \n",
       "17                  1/1/2015 11:47              319   \n",
       "18                  3/7/2017 22:08               27   \n",
       "19  Tue Sep 30 04:56:09 +0000 2014                0   \n",
       "20  Sun Sep 29 21:35:03 +0000 2013              328   \n",
       "21  Sat May 03 19:04:37 +0000 2014                4   \n",
       "22  Thu Sep 26 21:22:10 +0000 2013                2   \n",
       "23  Tue Oct 27 23:06:22 +0000 2015                4   \n",
       "24                 7/10/2015 18:30                6   \n",
       "\n",
       "                                verified statuses_count   lang  \\\n",
       "0   \"\"\"Mon Jan 02 02:25:26 +0000 2017\"\"\"              0  FALSE   \n",
       "1                                  FALSE             31     en   \n",
       "2                                  FALSE            713     en   \n",
       "3                                  FALSE            676     en   \n",
       "4                                  FALSE            185     en   \n",
       "5                                  FALSE             11     en   \n",
       "6                                  FALSE           6068     en   \n",
       "7                                  FALSE           2597     en   \n",
       "8                                  FALSE           9922     en   \n",
       "9                                  FALSE           2515     en   \n",
       "10                                 FALSE            111     en   \n",
       "11                                 FALSE            230     en   \n",
       "12                                 FALSE              0     en   \n",
       "13                                 FALSE              0     en   \n",
       "14                                 FALSE              0     en   \n",
       "15                                 FALSE          16067     en   \n",
       "16                                 FALSE          31721     en   \n",
       "17                                 FALSE            406     en   \n",
       "18                                 FALSE              0     th   \n",
       "19                                 FALSE          21506     en   \n",
       "20                                 FALSE          65022     en   \n",
       "21                                 FALSE           6230     en   \n",
       "22                                 FALSE          30156     en   \n",
       "23                                 FALSE           7640     en   \n",
       "24                                 FALSE           9584     en   \n",
       "\n",
       "                                               status  \\\n",
       "0                                               78554   \n",
       "1                                                null   \n",
       "2   \"{'retweeted': False, 'is_quote_status': False...   \n",
       "3   Construction of human anti-tetanus single-chai...   \n",
       "4                                                null   \n",
       "5   \"Status(_api=<tweepy.api.API object at 0x10192...   \n",
       "6   \"{u'contributors': None, u'truncated': False, ...   \n",
       "7   \"{u'contributors': None, u'truncated': False, ...   \n",
       "8   Feeding Schedule And Proteolysis Regulate Auto...   \n",
       "9   Functional Selectivity in Cytokine Signaling R...   \n",
       "10  \"{u'contributors': None, u'truncated': False, ...   \n",
       "11  ArXiv: Large-scale chromosome folding versus g...   \n",
       "12                                               None   \n",
       "13                                               None   \n",
       "14                                               None   \n",
       "15  \"{u'contributors': None, u'truncated': False, ...   \n",
       "16  \"{u'contributors': None, u'truncated': False, ...   \n",
       "17                                               null   \n",
       "18                                               None   \n",
       "19  \"{u'contributors': None, u'truncated': False, ...   \n",
       "20  \"{u'contributors': None, u'truncated': False, ...   \n",
       "21  \"{u'contributors': None, u'truncated': False, ...   \n",
       "22  \"{u'contributors': None, u'truncated': False, ...   \n",
       "23  \"{'favorited': False, 'id_str': '8411371549149...   \n",
       "24  \"Status(in_reply_to_user_id=None, place=None, ...   \n",
       "\n",
       "                             default_profile  \\\n",
       "0                                   \"\"\"en\"\"\"   \n",
       "1                                       TRUE   \n",
       "2                         'truncated': False   \n",
       "3                                       TRUE   \n",
       "4                                      FALSE   \n",
       "5              'in_reply_to_status_id': None   \n",
       "6                        u'retweeted': False   \n",
       "7                        u'retweeted': False   \n",
       "8                                      FALSE   \n",
       "9                                       TRUE   \n",
       "10                       u'retweeted': False   \n",
       "11                                      TRUE   \n",
       "12                                      TRUE   \n",
       "13                                      TRUE   \n",
       "14                                      TRUE   \n",
       "15                       u'retweeted': False   \n",
       "16                       u'retweeted': False   \n",
       "17                                     FALSE   \n",
       "18                                      TRUE   \n",
       "19                       u'retweeted': False   \n",
       "20                       u'retweeted': False   \n",
       "21   u'quoted_status_id': 765179912265146368   \n",
       "22                       u'retweeted': False   \n",
       "23                               jerks.</a>'   \n",
       "24             'in_reply_to_status_id': None   \n",
       "\n",
       "                                default_profile_image  \\\n",
       "0   \"{      \"\"created_at\"\": \"\"Sun Mar 12 15:44:04 ...   \n",
       "1                                               FALSE   \n",
       "2                         'in_reply_to_user_id': None   \n",
       "3                                                TRUE   \n",
       "4                                               FALSE   \n",
       "5                   'in_reply_to_status_id_str': None   \n",
       "6                                u'coordinates': None   \n",
       "7                                u'coordinates': None   \n",
       "8                                               FALSE   \n",
       "9                                               FALSE   \n",
       "10                               u'coordinates': None   \n",
       "11                                               TRUE   \n",
       "12                                               TRUE   \n",
       "13                                               TRUE   \n",
       "14                                               TRUE   \n",
       "15                               u'coordinates': None   \n",
       "16                               u'coordinates': None   \n",
       "17                                              FALSE   \n",
       "18                                               TRUE   \n",
       "19                               u'coordinates': None   \n",
       "20                               u'coordinates': None   \n",
       "21                                u'retweeted': False   \n",
       "22                               u'coordinates': None   \n",
       "23                                        'geo': None   \n",
       "24         'text': '_ÙÔö _Ù÷À _Ù_ _ÙÁ #emoji #poll'   \n",
       "\n",
       "                               has_extended_profile  \\\n",
       "0                        \"\"id\"\": 840951532543737900   \n",
       "1                                             FALSE   \n",
       "2    'created_at': 'Mon Mar 13 16:00:00 +0000 2017'   \n",
       "3                                             FALSE   \n",
       "4                                             FALSE   \n",
       "5                       'in_reply_to_user_id': None   \n",
       "6                      u'entities': {u'symbols': []   \n",
       "7                      u'entities': {u'symbols': []   \n",
       "8                                             FALSE   \n",
       "9                                             FALSE   \n",
       "10                     u'entities': {u'symbols': []   \n",
       "11                                            FALSE   \n",
       "12                                             None   \n",
       "13                                             None   \n",
       "14                                             None   \n",
       "15                     u'entities': {u'symbols': []   \n",
       "16                     u'entities': {u'symbols': []   \n",
       "17                                            FALSE   \n",
       "18                                            FALSE   \n",
       "19                     u'entities': {u'symbols': []   \n",
       "20                     u'entities': {u'symbols': []   \n",
       "21                             u'coordinates': None   \n",
       "22                     u'entities': {u'symbols': []   \n",
       "23                      'possibly_sensitive': False   \n",
       "24                              'coordinates': None   \n",
       "\n",
       "                                        name  \\\n",
       "0         \"\"id_str\"\": \"\"840951532543737856\"\"   \n",
       "1                                Keri Nelson   \n",
       "2                       'contributors': None   \n",
       "3                         single cell papers   \n",
       "4                               lucarivera16   \n",
       "5            'in_reply_to_user_id_str': None   \n",
       "6                       u'user_mentions': []   \n",
       "7                       u'user_mentions': []   \n",
       "8                           Autophagy Papers   \n",
       "9                              Hematopoiesis   \n",
       "10                      u'user_mentions': []   \n",
       "11                                 gyrpapers   \n",
       "12                               Lavon Court   \n",
       "13                                 Alen Alex   \n",
       "14                          Rajesh prajapati   \n",
       "15                      u'user_mentions': []   \n",
       "16                      u'user_mentions': []   \n",
       "17                           yeahTonyaGonzal   \n",
       "18                                       xXx   \n",
       "19     u'user_mentions': [{u'id': 2447379738   \n",
       "20       u'user_mentions': [{u'id': 85789543   \n",
       "21              u'entities': {u'symbols': []   \n",
       "22                      u'user_mentions': []   \n",
       "23                   'entities': {'urls': []   \n",
       "24              'in_reply_to_user_id': None}   \n",
       "\n",
       "                                                  bot  \n",
       "0         \"\"text\"\": \"\"[Southeast Houston] Chansey ...  \n",
       "1                                                   1  \n",
       "2                   'in_reply_to_status_id_str': None  \n",
       "3                                                   1  \n",
       "4                                                   1  \n",
       "5                     'in_reply_to_screen_name': None  \n",
       "6                                     u'hashtags': []  \n",
       "7                                     u'hashtags': []  \n",
       "8                                                   1  \n",
       "9                                                   1  \n",
       "10                                    u'hashtags': []  \n",
       "11                                                  1  \n",
       "12                                                  1  \n",
       "13                                                  1  \n",
       "14                                                  1  \n",
       "15                                    u'hashtags': []  \n",
       "16                                    u'hashtags': []  \n",
       "17                                                  1  \n",
       "18                                                  1  \n",
       "19                                     u'indices': [0  \n",
       "20                                     u'indices': [0  \n",
       "21                               u'user_mentions': []  \n",
       "22                                    u'hashtags': []  \n",
       "23                          'media': [{'indices': [20  \n",
       "24                              is_quote_status=False  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.limit(25) .toPandas ()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many lines have missing values? run the next command to figure it out! \n",
    "\n",
    "```python\n",
    "import pyspark.sql.functions as f\n",
    "from functools import reduce\n",
    "df.where(reduce(lambda x, y: x | y, (f.col(x).isNull() for x in df.columns))).count()\n",
    "```\n",
    "\n",
    "> [functools](https://docs.python.org/3/library/functools.html) is a python 3 library.\n",
    "> \n",
    "> [reduce](https://docs.python.org/3/library/functools.html?highlight=reduce#functools.reduce) is part of functools, it takes two arguments: x and y, and produce cumulative items of iterable - in our case: `x | y`\n",
    "> `|` is python OR operator, we concat x and y functionality with OR operator\n",
    "\n",
    "> For example, reduce(lambda x, y: x+y, [1, 2, 3, 4, 5]) calculates ((((1+2)+3)+4)+5)\n",
    "\n",
    "\n",
    "Run only reduce function and check the output:\n",
    "\n",
    "`reduce(lambda x, y: x | y, (f.col(x).isNull() for x in df.columns))`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<b'((((((((((((((((((((id IS NULL) OR (id_str IS NULL)) OR (screen_name IS NULL)) OR (location IS NULL)) OR (description IS NULL)) OR (url IS NULL)) OR (followers_count IS NULL)) OR (friends_count IS NULL)) OR (listed_count IS NULL)) OR (created_at IS NULL)) OR (favourites_count IS NULL)) OR (verified IS NULL)) OR (statuses_count IS NULL)) OR (lang IS NULL)) OR (status IS NULL)) OR (default_profile IS NULL)) OR (default_profile_image IS NULL)) OR (has_extended_profile IS NULL)) OR (name IS NULL)) OR (bot IS NULL))'>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pyspark.sql.functions as f\n",
    "from functools import reduce\n",
    "reduce(lambda x, y: x | y, (f.col(x).isNull() for x in df.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You created a concatenation of `OR` operators with `IS NULL` functionality for all the columns!\n",
    "\n",
    "Now, put it together:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1780"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pyspark.sql.functions as f\n",
    "from functools import reduce\n",
    "\n",
    "reducePhrase = reduce(lambda x, y: x | y, (f.col(x).isNull() for x in df.columns))\n",
    "\n",
    "df.where(reducePhrase).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Distinct Value\n",
    "\n",
    "Get the sum  of `id` distinct values, it should be equal to the size of the data \n",
    "\n",
    "Try both `id` and `id_str` fields.\n",
    "\n",
    "Use the next code and adjust it according to the field:\n",
    "\n",
    "```pythob\n",
    "df.select(\"field_name\").distinct().count()\n",
    "```\n",
    "\n",
    "\n",
    "What happened here? Is it in the same size of the data set?\n",
    "Don't worry; We fix that soon!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2403"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select(\"id\").distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2439"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select(\"id_str\").distinct().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Is Null\n",
    "\n",
    "How many rows have null on the `screen_name` column?\n",
    "\n",
    "Use the `where` with col `.isNull` function to get the DataFrame with null value for `column_name`.\n",
    "\n",
    "Count it! Use the count method for that.\n",
    "\n",
    "Code sample:\n",
    "```python\n",
    "df.where(f.col('column_name').isNull()).count()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.where(f.col('screen_name').isNull()).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details><summary>Answer</summary>\n",
    "<p>\n",
    "\n",
    "#### 5\n",
    "\n",
    "</p>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standard Deviation\n",
    "\n",
    "As part of exploring the data phase, the standard deviation(stddev) is a must!\n",
    "\n",
    "Calculate **stddev** for `followers_count`.\n",
    "\n",
    "### Notice! \n",
    "Some rows have None/Null for `followers_count`, we can:\n",
    "\n",
    "1. Ignore and not calculate the stddev for them\n",
    "\n",
    "**OR** \n",
    "\n",
    "2. Give them a default value\n",
    "\n",
    "**OR** \n",
    "\n",
    "3. Filter them entirely out of our training data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Start with counting how many rows has null for followers_count:\n",
    "\n",
    "Run this:\n",
    "```python\n",
    "df.where(f.col('followers_count').isNull()).count()\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.where(f.col('followers_count').isNull()).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We go with:  `2. Give them a default value`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Give deafult values with - Fill null values - fillna()\n",
    "\n",
    "Give the null cells a default value:\n",
    "Using [fillna](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame.fillna)\n",
    "\n",
    "Notice the matching type request.\n",
    "Meaning, if a column is of type string, we will need a default value of type string.\n",
    "At the moment, all are fields are of type string.\n",
    "\n",
    "Code sample:\n",
    "```python\n",
    "df_defaultvalue = df.fillna({'column_name':'0'})\n",
    "```\n",
    "\n",
    "<details><summary>Answer</summary>\n",
    "<p>\n",
    "\n",
    "df_defaultvalue = df.fillna({'followers_count':'0'})\n",
    "\n",
    "</p>\n",
    "</details>\n",
    "\n",
    "Remember to valide yourself with count:\n",
    "\n",
    "```python\n",
    "df_defaultvalue.where(f.col('followers_count').isNull()).count()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_defaultvalue = df.fillna({'followers_count':0})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_defaultvalue.where(f.col('followers_count').isNull()).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2nd phase of **standard deviation** calculation is:\n",
    "\n",
    "Casting data to numbers!\n",
    "\n",
    "Cast it to integer:\n",
    "\n",
    "In the code sample, replace the `column_name` with `followers_count`:\n",
    "```python\n",
    "from pyspark.sql.types import IntegerType\n",
    "\n",
    "data_df = df_defaultvalue.withColumn(\"column_name\", df_defaultvalue[\"column_name\"].cast(IntegerType()))\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import IntegerType\n",
    "\n",
    "data_df = df_defaultvalue.withColumn(\"followers_count\", df_defaultvalue[\"followers_count\"].cast(IntegerType()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate Standard Deviation! \n",
    "\n",
    "Use `pyspark.sql.function` methods, [here are the docs](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#module-pyspark.sql.functions)\n",
    "\n",
    "Check out **describe** functionality. it provides us `count`, `mean`, `stddev`, `min` and `max` calculations in one function!\n",
    "\n",
    "**Remember** - Use the last DataFrame that you created, with the casting and default values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "`describe` can take any field, or calculate statistics for all fields.\n",
    "\n",
    "Code Example:\n",
    "```python\n",
    "df.describe(['age']).show()\n",
    "df.describe().toPandas().transpose()\n",
    "\n",
    "```\n",
    "\n",
    "In the code example, Change `age` to `followers_count` and run it!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------------+\n",
      "|summary|  followers_count|\n",
      "+-------+-----------------+\n",
      "|  count|             2781|\n",
      "|   mean|995260.6181229773|\n",
      "| stddev|5604474.389024293|\n",
      "|    min|                0|\n",
      "|    max|         96321564|\n",
      "+-------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "data_df.describe(['followers_count']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>summary</th>\n",
       "      <td>count</td>\n",
       "      <td>mean</td>\n",
       "      <td>stddev</td>\n",
       "      <td>min</td>\n",
       "      <td>max</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>2840</td>\n",
       "      <td>1.48245978897998496E17</td>\n",
       "      <td>3.1005777294299482E17</td>\n",
       "      <td>}</td>\n",
       "      <td>«I started something I couldn’t finish - The S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_str</th>\n",
       "      <td>2836</td>\n",
       "      <td>1.42975365019680048E17</td>\n",
       "      <td>3.0581818876766093E17</td>\n",
       "      <td>visit http://t.co/o5SYTE11ku.\"</td>\n",
       "      <td>https://t.co/wfdAJoxfYZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>screen_name</th>\n",
       "      <td>2835</td>\n",
       "      <td>539.5</td>\n",
       "      <td>660.4263774259778</td>\n",
       "      <td>\"\"\"1lovetakes2\"\"\"</td>\n",
       "      <td>ÓATVI_ABÒ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>location</th>\n",
       "      <td>1816</td>\n",
       "      <td>11242.444444444445</td>\n",
       "      <td>31093.281232088997</td>\n",
       "      <td></td>\n",
       "      <td>€¡stanbul, TÌ_rkiye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description</th>\n",
       "      <td>2433</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.899494936611665</td>\n",
       "      <td>CA\"\"\"</td>\n",
       "      <td>‰÷Ï_ÙÕ_  ì_¥Ð _ö´‰¼§â‰_£_¥  _ÙÔ_ÙÕ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>url</th>\n",
       "      <td>1540</td>\n",
       "      <td>0.5142857142857142</td>\n",
       "      <td>0.6584933146039916</td>\n",
       "      <td>82% notification for all top tiered pokemon</td>\n",
       "      <td>the force is with me\"\" - dead blind guy\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>followers_count</th>\n",
       "      <td>2781</td>\n",
       "      <td>995260.6181229773</td>\n",
       "      <td>5604474.389024293</td>\n",
       "      <td>0</td>\n",
       "      <td>96321564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>friends_count</th>\n",
       "      <td>2797</td>\n",
       "      <td>11659.697800216372</td>\n",
       "      <td>266249.06763620034</td>\n",
       "      <td>European-style board games</td>\n",
       "      <td>https://t.co/BkV5HL3bmA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>listed_count</th>\n",
       "      <td>2797</td>\n",
       "      <td>3238.1019748653503</td>\n",
       "      <td>17316.69703229031</td>\n",
       "      <td>#lifestyle #exercise #food #holistic #natural\"\"\"</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>created_at</th>\n",
       "      <td>2797</td>\n",
       "      <td>1739.0</td>\n",
       "      <td>7180.937650367223</td>\n",
       "      <td>AB Studios\"\"\"</td>\n",
       "      <td>zh-CN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>favourites_count</th>\n",
       "      <td>2797</td>\n",
       "      <td>2006.8162968352128</td>\n",
       "      <td>16182.205112382257</td>\n",
       "      <td>Ngacir Gan! |ps : twit kami jgn terlalu diser...</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>verified</th>\n",
       "      <td>2797</td>\n",
       "      <td>4943.66</td>\n",
       "      <td>10599.861409586872</td>\n",
       "      <td>'in_reply_to_status_id': None</td>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>statuses_count</th>\n",
       "      <td>2797</td>\n",
       "      <td>19391.33357584576</td>\n",
       "      <td>155670.25531125066</td>\n",
       "      <td>'entities': {'hashtags': []</td>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lang</th>\n",
       "      <td>2797</td>\n",
       "      <td>18955.891304347828</td>\n",
       "      <td>28048.779067290983</td>\n",
       "      <td>'entities': {'hashtags': []</td>\n",
       "      <td>zh-tw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>status</th>\n",
       "      <td>2763</td>\n",
       "      <td>11232.733333333334</td>\n",
       "      <td>21588.407505837367</td>\n",
       "      <td>'in_reply_to_user_id_str': None</td>\n",
       "      <td>totally understand that people are busy but yo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>default_profile</th>\n",
       "      <td>2797</td>\n",
       "      <td>1703.2</td>\n",
       "      <td>3118.4729756725487</td>\n",
       "      <td>\"\"id\"\": 344571115206754300</td>\n",
       "      <td>en</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>default_profile_image</th>\n",
       "      <td>2797</td>\n",
       "      <td>879.6666666666666</td>\n",
       "      <td>585.7496621140012</td>\n",
       "      <td>\"\"id\"\": 695408893535453200</td>\n",
       "      <td>TRUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>has_extended_profile</th>\n",
       "      <td>2750</td>\n",
       "      <td>254.66666666666666</td>\n",
       "      <td>362.37043661608675</td>\n",
       "      <td>\"\"id\"\": 810973039659782100</td>\n",
       "      <td>‰Û I mutter.'</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>2797</td>\n",
       "      <td>16891.666666666668</td>\n",
       "      <td>29243.368912171067</td>\n",
       "      <td>\"\"id\"\": 840756664257130500</td>\n",
       "      <td>íŠder Franco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bot</th>\n",
       "      <td>2797</td>\n",
       "      <td>0.7156673114119922</td>\n",
       "      <td>1.1690477596307185</td>\n",
       "      <td>\"\"entities\"\":  {        \"\"hashtags\"\":  [...</td>\n",
       "      <td>_Ù_\\n_ÑÐ_Ñ¨_Ñ__Ñ__ÑÀ_Ñ¦_Ñ__÷Û 676  \\n...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0                       1                      2  \\\n",
       "summary                count                    mean                 stddev   \n",
       "id                      2840  1.48245978897998496E17  3.1005777294299482E17   \n",
       "id_str                  2836  1.42975365019680048E17  3.0581818876766093E17   \n",
       "screen_name             2835                   539.5      660.4263774259778   \n",
       "location                1816      11242.444444444445     31093.281232088997   \n",
       "description             2433                     9.0      9.899494936611665   \n",
       "url                     1540      0.5142857142857142     0.6584933146039916   \n",
       "followers_count         2781       995260.6181229773      5604474.389024293   \n",
       "friends_count           2797      11659.697800216372     266249.06763620034   \n",
       "listed_count            2797      3238.1019748653503      17316.69703229031   \n",
       "created_at              2797                  1739.0      7180.937650367223   \n",
       "favourites_count        2797      2006.8162968352128     16182.205112382257   \n",
       "verified                2797                 4943.66     10599.861409586872   \n",
       "statuses_count          2797       19391.33357584576     155670.25531125066   \n",
       "lang                    2797      18955.891304347828     28048.779067290983   \n",
       "status                  2763      11232.733333333334     21588.407505837367   \n",
       "default_profile         2797                  1703.2     3118.4729756725487   \n",
       "default_profile_image   2797       879.6666666666666      585.7496621140012   \n",
       "has_extended_profile    2750      254.66666666666666     362.37043661608675   \n",
       "name                    2797      16891.666666666668     29243.368912171067   \n",
       "bot                     2797      0.7156673114119922     1.1690477596307185   \n",
       "\n",
       "                                                                       3  \\\n",
       "summary                                                              min   \n",
       "id                                                                     }   \n",
       "id_str                                    visit http://t.co/o5SYTE11ku.\"   \n",
       "screen_name                                            \"\"\"1lovetakes2\"\"\"   \n",
       "location                                                                   \n",
       "description                                                        CA\"\"\"   \n",
       "url                          82% notification for all top tiered pokemon   \n",
       "followers_count                                                        0   \n",
       "friends_count                                 European-style board games   \n",
       "listed_count            #lifestyle #exercise #food #holistic #natural\"\"\"   \n",
       "created_at                                                 AB Studios\"\"\"   \n",
       "favourites_count        Ngacir Gan! |ps : twit kami jgn terlalu diser...   \n",
       "verified                                   'in_reply_to_status_id': None   \n",
       "statuses_count                               'entities': {'hashtags': []   \n",
       "lang                                         'entities': {'hashtags': []   \n",
       "status                                   'in_reply_to_user_id_str': None   \n",
       "default_profile                               \"\"id\"\": 344571115206754300   \n",
       "default_profile_image                         \"\"id\"\": 695408893535453200   \n",
       "has_extended_profile                          \"\"id\"\": 810973039659782100   \n",
       "name                                          \"\"id\"\": 840756664257130500   \n",
       "bot                          \"\"entities\"\":  {        \"\"hashtags\"\":  [...   \n",
       "\n",
       "                                                                       4  \n",
       "summary                                                              max  \n",
       "id                     «I started something I couldn’t finish - The S...  \n",
       "id_str                                           https://t.co/wfdAJoxfYZ  \n",
       "screen_name                                                    ÓATVI_ABÒ  \n",
       "location                                             €¡stanbul, TÌ_rkiye  \n",
       "description                   ‰÷Ï_ÙÕ_  ì_¥Ð _ö´‰¼§â‰_£_¥  _ÙÔ_ÙÕ  \n",
       "url                             the force is with me\"\" - dead blind guy\"  \n",
       "followers_count                                                 96321564  \n",
       "friends_count                                    https://t.co/BkV5HL3bmA  \n",
       "listed_count                                                        null  \n",
       "created_at                                                         zh-CN  \n",
       "favourites_count                                                      en  \n",
       "verified                                                            TRUE  \n",
       "statuses_count                                                      TRUE  \n",
       "lang                                                               zh-tw  \n",
       "status                 totally understand that people are busy but yo...  \n",
       "default_profile                                                       en  \n",
       "default_profile_image                                               TRUE  \n",
       "has_extended_profile                                      ‰Û I mutter.'  \n",
       "name                                                        íŠder Franco  \n",
       "bot                    _Ù_\\n_ÑÐ_Ñ¨_Ñ__Ñ__ÑÀ_Ñ¦_Ñ__÷Û 676  \\n...  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.describe().toPandas().transpose()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data is dirty! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Have you noticed a weird behavior with `id` and `id_str`?\n",
    "\n",
    "Run `.distinct().count()` on each, and count how many blank values there are there.\n",
    "\n",
    "Who has the most distinct values? Is it the same as the DataFrame?\n",
    "\n",
    "\n",
    "Use the code sample and remember to replace column name accordinly\n",
    "```python\n",
    "df.select(\"id_str\").distinct().count()\n",
    "df.select(\"id\").distinct().count()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.where(f.col('id_str').isNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.where(f.col('id').isNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2439"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select(\"id_str\").distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2403"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select(\"id\").distinct().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You probably discovered that we couldn't trust `id` nor `id_str` !\n",
    "\n",
    "Oops! What should we do? Do we need them at all?\n",
    "\n",
    "Continue to Excercise 3! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 3: Filter the DataFrame "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You have reached the last section of cleaning and preparing the data 🎊\n",
    "\n",
    "\n",
    "In this exercise - you filter, cast, and add a default value to necessary fields using the Spark functionality.\n",
    "\n",
    "You are going to use the DataFrame that you created in chapters (2,3, and 4!)📙\n",
    "\n",
    "Follow the instructions. For any questions, please use 👉 the Q&A chat.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Start with casting:\n",
    "Run the next commands:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import IntegerType, BooleanType\n",
    "\n",
    "casted_df = data_df.withColumn(\"friends_count\", data_df[\"friends_count\"].cast(IntegerType()))\n",
    "casted_df = casted_df.withColumn(\"listed_count\", casted_df[\"listed_count\"].cast(IntegerType()))\n",
    "casted_df = casted_df.withColumn(\"favourites_count\", casted_df[\"favourites_count\"].cast(IntegerType()))\n",
    "casted_df = casted_df.withColumn(\"statuses_count\", casted_df[\"statuses_count\"].cast(IntegerType()))\n",
    "casted_df = casted_df.withColumn(\"verified\", casted_df[\"verified\"].cast(BooleanType()))\n",
    "casted_df = casted_df.withColumn(\"default_profile\", casted_df[\"default_profile\"].cast(BooleanType()))\n",
    "casted_df = casted_df.withColumn(\"has_extended_profile\", casted_df[\"has_extended_profile\"].cast(BooleanType()))\n",
    "casted_df = casted_df.withColumn(\"default_profile_image\", casted_df[\"default_profile_image\"].cast(BooleanType()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happened here? check it out:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "casted_df.limit(5) .toPandas()\n",
    "df = casted_df "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ **Task :** \n",
    "\n",
    "#### When and withColumn functionality\n",
    "\n",
    "Let's fix the weird behavior of `id_str` and `id` fields.\n",
    "\n",
    "\n",
    "Now that we know that there are some blanks values for `id_str`, let's try to fill them out with `id` values.\n",
    "\n",
    "For achiving that, we will use the `when` functions:\n",
    "\n",
    "```python\n",
    "from pyspark.sql.functions import when\n",
    "new_df = df.select(when(df['age'].isNull(), 3).otherwise(df['age']))\n",
    "\n",
    "```\n",
    "\n",
    "Use `when` with the `withColumn` functionality:\n",
    "\n",
    "```python\n",
    "from pyspark.sql.functions import withColumn\n",
    "new_df = df.withColumn('age2', df.age + 2)\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "Put `where` and `withColumn` together:\n",
    "\n",
    "```python\n",
    " new_df = df.withColumn('new_column_name',when(df['column_that_we_check'].isNotNull(),df['colum_if_true']).\n",
    "                        otherwise(df['column_if_false']))\n",
    "```\n",
    "\n",
    "\n",
    "Replace **age** column from the examples with `id_str` and `id` according to the needs.\n",
    "\n",
    "\n",
    "**Remember!** DataFrame is an immutable object. Each function on DataFrame that transform it creates another DataFrame and returns a pointer to the new one. Remember to work with your latest DataFrame and validate yourself! \n",
    "\n",
    "\n",
    "\n",
    "[Docs for when](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.functions.when), \n",
    "[Docs for withColumn](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.functions.withColumn)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import when\n",
    "test = df.withColumn('id_str',when(df['id'].isNotNull(),df['id']).otherwise(df['id_str']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details><summary>Hint</summary>\n",
    "<p>\n",
    "\n",
    "Use the isNull function with when, like this:\n",
    "    \n",
    "```python\n",
    "when(df['id_str'].isNull(),df['id']).otherwise(df['id_str'])\n",
    "\n",
    "```  \n",
    "\n",
    "</p>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details><summary>Answer</summary>\n",
    "<p>\n",
    "    \n",
    "```python\n",
    "from pyspark.sql.functions import when\n",
    "test = df.withColumn('id_str',when(df['id_str'].isNull(),df['id']).otherwise(df['id_str']))\n",
    "\n",
    "```  \n",
    "</p>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Happy with the results? save erase your old DataFrame with the next command:\n",
    "df = test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2403"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# validate yourself\n",
    "df.select(\"id_str\").distinct().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ **Task :** \n",
    "\n",
    "#### drop functionality\n",
    "\n",
    "Drop column `id` with drop function:\n",
    "    \n",
    "```python\n",
    "   new_df = df.drop('column_name')\n",
    "```\n",
    "\n",
    "And validate the schema for the new DataFrame\n",
    "\n",
    "```python\n",
    "  new_df.schema\n",
    "```\n",
    "\n",
    "After validating the new DataFrame, overwrite the reference to clear memory:\n",
    "\n",
    "```python\n",
    "   df = new_df\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = df.drop('id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ **Task :**  \n",
    "\n",
    "Drop the next fields:\n",
    "    * `default_profile_image`\n",
    "    * `has_extended_profile`\n",
    "    * `url`\n",
    "    * `created_at`\n",
    "    \n",
    "    You can drop field by field, or provide all the fields to drop in one function call.\n",
    "    \n",
    "```python\n",
    "    updated_df = df.drop('age','history','another_column_to_drop')\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "**Remember** to validate yourself with the schema and overwrite the DataFrame reference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = df.drop('default_profile_image','has_extended_profile','url','created_at','lang')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.16E+17</td>\n",
       "      <td>\"\"\"HoustonPokeMap\"\"\"</td>\n",
       "      <td>\"\"\"Houston</td>\n",
       "      <td>TX\"\"\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1291</td>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>78554</td>\n",
       "      <td>None</td>\n",
       "      <td>\"\"id_str\"\": \"\"840951532543737856\"\"</td>\n",
       "      <td>\"\"text\"\": \"\"[Southeast Houston] Chansey ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4843621225</td>\n",
       "      <td>kernyeahx</td>\n",
       "      <td>Templeville town, MD, USA</td>\n",
       "      <td>From late 2014 Socium Marketplace will make sh...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>349.0</td>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "      <td>False</td>\n",
       "      <td>31</td>\n",
       "      <td>null</td>\n",
       "      <td>True</td>\n",
       "      <td>Keri Nelson</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4303727112</td>\n",
       "      <td>mattlieberisbot</td>\n",
       "      <td>None</td>\n",
       "      <td>Inspired by the smart, funny folks at @replyal...</td>\n",
       "      <td>1086.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>713</td>\n",
       "      <td>\"{'retweeted': False, 'is_quote_status': False...</td>\n",
       "      <td>None</td>\n",
       "      <td>'contributors': None</td>\n",
       "      <td>'in_reply_to_status_id_str': None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3063139353</td>\n",
       "      <td>sc_papers</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>676</td>\n",
       "      <td>Construction of human anti-tetanus single-chai...</td>\n",
       "      <td>True</td>\n",
       "      <td>single cell papers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2955142070</td>\n",
       "      <td>lucarivera16</td>\n",
       "      <td>Dublin, United States</td>\n",
       "      <td>Inspiring cooks everywhere since 1956.</td>\n",
       "      <td>11.0</td>\n",
       "      <td>745.0</td>\n",
       "      <td>0</td>\n",
       "      <td>146</td>\n",
       "      <td>False</td>\n",
       "      <td>185</td>\n",
       "      <td>null</td>\n",
       "      <td>False</td>\n",
       "      <td>lucarivera16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8.41E+17</td>\n",
       "      <td>dantheimprover</td>\n",
       "      <td>Austin, TX</td>\n",
       "      <td>Just a guy trying to do good by telling everyo...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>186.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>\"Status(_api=&lt;tweepy.api.API object at 0x10192...</td>\n",
       "      <td>None</td>\n",
       "      <td>'in_reply_to_user_id_str': None</td>\n",
       "      <td>'in_reply_to_screen_name': None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2482834658</td>\n",
       "      <td>_all_of_us_</td>\n",
       "      <td>in a machine.</td>\n",
       "      <td>bot by @rubicon</td>\n",
       "      <td>193.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>6068</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>None</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3333573622</td>\n",
       "      <td>KatamariItems</td>\n",
       "      <td>None</td>\n",
       "      <td>[Bot rolled up by @BeachEpisode] Cataloguing e...</td>\n",
       "      <td>8227.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>89</td>\n",
       "      <td>26</td>\n",
       "      <td>False</td>\n",
       "      <td>2597</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>None</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2996105102</td>\n",
       "      <td>AutophagyPapers</td>\n",
       "      <td>None</td>\n",
       "      <td>Twitterbot for #Autophagy papers. Curated by @...</td>\n",
       "      <td>275.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17</td>\n",
       "      <td>23</td>\n",
       "      <td>False</td>\n",
       "      <td>9922</td>\n",
       "      <td>Feeding Schedule And Proteolysis Regulate Auto...</td>\n",
       "      <td>False</td>\n",
       "      <td>Autophagy Papers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3271095818</td>\n",
       "      <td>HSC_papers</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>51.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>2515</td>\n",
       "      <td>Functional Selectivity in Cytokine Signaling R...</td>\n",
       "      <td>True</td>\n",
       "      <td>Hematopoiesis</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2717905134</td>\n",
       "      <td>everycheese</td>\n",
       "      <td>cheese land</td>\n",
       "      <td>cheese cheese cheese // updates every 4 hours ...</td>\n",
       "      <td>51.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>111</td>\n",
       "      <td>\"{u'contributors': None, u'truncated': False, ...</td>\n",
       "      <td>None</td>\n",
       "      <td>u'user_mentions': []</td>\n",
       "      <td>u'hashtags': []</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2826073429</td>\n",
       "      <td>gyr_papers</td>\n",
       "      <td>None</td>\n",
       "      <td>Literature bot searching arXiv, biorXiv, PeerJ...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>230</td>\n",
       "      <td>ArXiv: Large-scale chromosome folding versus g...</td>\n",
       "      <td>True</td>\n",
       "      <td>gyrpapers</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8.10E+17</td>\n",
       "      <td>lavon_court</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>Lavon Court</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1027583707</td>\n",
       "      <td>AjstyleAlen</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>Alen Alex</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>394991274</td>\n",
       "      <td>Rupesh93505</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>Rajesh prajapati</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id_str           screen_name                   location  \\\n",
       "0     8.16E+17  \"\"\"HoustonPokeMap\"\"\"                 \"\"\"Houston   \n",
       "1   4843621225             kernyeahx  Templeville town, MD, USA   \n",
       "2   4303727112       mattlieberisbot                       None   \n",
       "3   3063139353             sc_papers                       None   \n",
       "4   2955142070          lucarivera16      Dublin, United States   \n",
       "5     8.41E+17        dantheimprover                 Austin, TX   \n",
       "6   2482834658           _all_of_us_              in a machine.   \n",
       "7   3333573622         KatamariItems                       None   \n",
       "8   2996105102       AutophagyPapers                       None   \n",
       "9   3271095818            HSC_papers                       None   \n",
       "10  2717905134           everycheese                cheese land   \n",
       "11  2826073429            gyr_papers                       None   \n",
       "12    8.10E+17           lavon_court                       None   \n",
       "13  1027583707           AjstyleAlen                       None   \n",
       "14   394991274           Rupesh93505                       None   \n",
       "\n",
       "                                          description  followers_count  \\\n",
       "0                                               TX\"\"\"              NaN   \n",
       "1   From late 2014 Socium Marketplace will make sh...              1.0   \n",
       "2   Inspired by the smart, funny folks at @replyal...           1086.0   \n",
       "3                                                None             33.0   \n",
       "4              Inspiring cooks everywhere since 1956.             11.0   \n",
       "5   Just a guy trying to do good by telling everyo...              1.0   \n",
       "6                                     bot by @rubicon            193.0   \n",
       "7   [Bot rolled up by @BeachEpisode] Cataloguing e...           8227.0   \n",
       "8   Twitterbot for #Autophagy papers. Curated by @...            275.0   \n",
       "9                                                None             51.0   \n",
       "10  cheese cheese cheese // updates every 4 hours ...             51.0   \n",
       "11  Literature bot searching arXiv, biorXiv, PeerJ...              2.0   \n",
       "12                                               None              0.0   \n",
       "13                                               None              1.0   \n",
       "14                                               None              0.0   \n",
       "\n",
       "    friends_count  listed_count  favourites_count verified  statuses_count  \\\n",
       "0             NaN          1291                10     None               0   \n",
       "1           349.0             0                38    False              31   \n",
       "2             0.0            14                 0    False             713   \n",
       "3             0.0             8                 0    False             676   \n",
       "4           745.0             0               146    False             185   \n",
       "5           186.0             0                 0    False              11   \n",
       "6             0.0            19                 0    False            6068   \n",
       "7             2.0            89                26    False            2597   \n",
       "8             0.0            17                23    False            9922   \n",
       "9             3.0             9                 0    False            2515   \n",
       "10            1.0            12                 0    False             111   \n",
       "11            1.0             4                 0    False             230   \n",
       "12           29.0             0                 0    False               0   \n",
       "13          206.0             0                 0    False               0   \n",
       "14           38.0             0                 0    False               0   \n",
       "\n",
       "                                               status default_profile  \\\n",
       "0                                               78554            None   \n",
       "1                                                null            True   \n",
       "2   \"{'retweeted': False, 'is_quote_status': False...            None   \n",
       "3   Construction of human anti-tetanus single-chai...            True   \n",
       "4                                                null           False   \n",
       "5   \"Status(_api=<tweepy.api.API object at 0x10192...            None   \n",
       "6   \"{u'contributors': None, u'truncated': False, ...            None   \n",
       "7   \"{u'contributors': None, u'truncated': False, ...            None   \n",
       "8   Feeding Schedule And Proteolysis Regulate Auto...           False   \n",
       "9   Functional Selectivity in Cytokine Signaling R...            True   \n",
       "10  \"{u'contributors': None, u'truncated': False, ...            None   \n",
       "11  ArXiv: Large-scale chromosome folding versus g...            True   \n",
       "12                                               None            True   \n",
       "13                                               None            True   \n",
       "14                                               None            True   \n",
       "\n",
       "                                        name  \\\n",
       "0         \"\"id_str\"\": \"\"840951532543737856\"\"   \n",
       "1                                Keri Nelson   \n",
       "2                       'contributors': None   \n",
       "3                         single cell papers   \n",
       "4                               lucarivera16   \n",
       "5            'in_reply_to_user_id_str': None   \n",
       "6                       u'user_mentions': []   \n",
       "7                       u'user_mentions': []   \n",
       "8                           Autophagy Papers   \n",
       "9                              Hematopoiesis   \n",
       "10                      u'user_mentions': []   \n",
       "11                                 gyrpapers   \n",
       "12                               Lavon Court   \n",
       "13                                 Alen Alex   \n",
       "14                          Rajesh prajapati   \n",
       "\n",
       "                                                  bot  \n",
       "0         \"\"text\"\": \"\"[Southeast Houston] Chansey ...  \n",
       "1                                                   1  \n",
       "2                   'in_reply_to_status_id_str': None  \n",
       "3                                                   1  \n",
       "4                                                   1  \n",
       "5                     'in_reply_to_screen_name': None  \n",
       "6                                     u'hashtags': []  \n",
       "7                                     u'hashtags': []  \n",
       "8                                                   1  \n",
       "9                                                   1  \n",
       "10                                    u'hashtags': []  \n",
       "11                                                  1  \n",
       "12                                                  1  \n",
       "13                                                  1  \n",
       "14                                                  1  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What did you get? Happy with the results?\n",
    "\n",
    "new_df.limit(15) .toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ **Task :**  \n",
    "\n",
    "#### Drop duplicates and describe functionality\n",
    "\n",
    "Sometimes, we get duplicated data accidentally, drop all duplicated by using the \n",
    "`dropDuplicates` function:\n",
    "    \n",
    "```python\n",
    "    new_df = df.dropDuplicates()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2840"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df = df.dropDuplicates()\n",
    "new_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `describe` to understand how the data looks like now.\n",
    "Remember that describe works only on numerical values.\n",
    "\n",
    "\n",
    "Use the next code sample and adjust it to your needs:\n",
    "```python\n",
    "new_df.describe('column_name').show()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+\n",
      "|summary|  favourites_count|\n",
      "+-------+------------------+\n",
      "|  count|              2749|\n",
      "|   mean|2006.8162968352128|\n",
      "| stddev|16182.205112382246|\n",
      "|    min|                 0|\n",
      "|    max|            714021|\n",
      "+-------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "new_df.describe('favourites_count').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Happy with the results?\n",
    "\n",
    "df = new_df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Machine Learning algorithm you are going to use doesn't take text/string as input. \n",
    "\n",
    "Hence, we transfer String columns to boolean or numerical.\n",
    "\n",
    "\n",
    "Turn all String columns to boolean or numerical, if not possible, drop them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ **Task :** \n",
    "\n",
    "Most of our data can be translated to _Integer_ , 1 for exist, 0 for non-exist.\n",
    "\n",
    "Implement that logic for the next columns:\n",
    "    * location\n",
    "    * status\n",
    "    * screen_name\n",
    "    * name\n",
    "    \n",
    "    \n",
    "    \n",
    "Code sample:\n",
    "```python\n",
    "df = df.withColumn('column_name',when(df['column_name'].isNull(),0).otherwise(1))\n",
    "```\n",
    "\n",
    "Run the next command to make it happen! \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.withColumn('location',when(df['location'].isNull(),0).otherwise(1))\n",
    "df = df.withColumn('status',when(df['status'].isNull(),0).otherwise(1))\n",
    "df = df.withColumn('screen_name',when(df['screen_name'].isNull(),0).otherwise(1))\n",
    "df = df.withColumn('name',when(df['name'].isNull(),0).otherwise(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ Task :\n",
    "\n",
    "Adapt `bot` column. \n",
    "\n",
    "`bot` is the data classification column, which indicated if the row represents a bot or not. \n",
    "\n",
    "1. Cast it into Integer.\n",
    "2. Set 1 or 0: 1 for bot and 0 for none bot.\n",
    "\n",
    "If we don't know what it is, use 0.\n",
    "\n",
    "Run the next commands, and remember to validate yourself!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>url</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>default_profile_image</th>\n",
       "      <th>has_extended_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3271095818</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>51</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>7/7/2015 15:23</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>2515</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>195370058</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>This is what I do. I drop truth bombs.</td>\n",
       "      <td>None</td>\n",
       "      <td>2925</td>\n",
       "      <td>3</td>\n",
       "      <td>139</td>\n",
       "      <td>9/26/2010 14:45</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>708</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2980456333</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>8063</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>Tue Jan 13 19:42:21 +0000 2015</td>\n",
       "      <td>42</td>\n",
       "      <td>False</td>\n",
       "      <td>119</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.82E+17</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>I luv Zendya. She is SO awesome. Shak it up is...</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun Oct 02 04:54:18 +0000 2016</td>\n",
       "      <td>75</td>\n",
       "      <td>False</td>\n",
       "      <td>125</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2719936344</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>•_£•__•_¢•_´•__•_£•_ø•_¨•_¨•_´•_£•_«•_©•_¨•_¤‹...</td>\n",
       "      <td>None</td>\n",
       "      <td>5744</td>\n",
       "      <td>3</td>\n",
       "      <td>112</td>\n",
       "      <td>Sat Aug 09 19:09:46 +0000 2014</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>105941</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_str  screen_name  location  \\\n",
       "0  3271095818            1         0   \n",
       "1   195370058            1         0   \n",
       "2  2980456333            1         0   \n",
       "3    7.82E+17            1         0   \n",
       "4  2719936344            1         1   \n",
       "\n",
       "                                         description   url  followers_count  \\\n",
       "0                                               None  None               51   \n",
       "1             This is what I do. I drop truth bombs.  None             2925   \n",
       "2                                               None  None             8063   \n",
       "3  I luv Zendya. She is SO awesome. Shak it up is...  None                2   \n",
       "4  •_£•__•_¢•_´•__•_£•_ø•_¨•_¨•_´•_£•_«•_©•_¨•_¤‹...  None             5744   \n",
       "\n",
       "   friends_count  listed_count                      created_at  \\\n",
       "0              3             9                  7/7/2015 15:23   \n",
       "1              3           139                 9/26/2010 14:45   \n",
       "2              1            13  Tue Jan 13 19:42:21 +0000 2015   \n",
       "3             51             0  Sun Oct 02 04:54:18 +0000 2016   \n",
       "4              3           112  Sat Aug 09 19:09:46 +0000 2014   \n",
       "\n",
       "   favourites_count  verified  statuses_count lang  status default_profile  \\\n",
       "0                 0     False            2515   en       1            True   \n",
       "1                 0     False             708   en       1            None   \n",
       "2                42     False             119   en       1            None   \n",
       "3                75     False             125   en       1            None   \n",
       "4                 2     False          105941   en       1            None   \n",
       "\n",
       "  default_profile_image has_extended_profile  name  bot  \n",
       "0                 False                False     1  1.0  \n",
       "1                  None                 None     1  NaN  \n",
       "2                  None                 None     1  NaN  \n",
       "3                  None                 None     1  NaN  \n",
       "4                  None                 None     1  NaN  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bot = df.withColumn('bot',df['bot'].cast(IntegerType()))\n",
    "df_bot.limit(5) .toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3366974463</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>A bot that creates hilarious joax via Google's...</td>\n",
       "      <td>1058</td>\n",
       "      <td>1</td>\n",
       "      <td>83</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>7678</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.19E+17</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Bot by @czircon. #botALLY</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>6832</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2651152393</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>daily writing assignments, posts twice a day ~...</td>\n",
       "      <td>148</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>1723</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2203838767</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>This is a bot to explain things like love and ...</td>\n",
       "      <td>116</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>8421</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1948423238</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>It's lonely out in space. Rookie @exoriders pa...</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>13622</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_str  screen_name  location  \\\n",
       "0  3366974463            1         1   \n",
       "1    7.19E+17            1         0   \n",
       "2  2651152393            1         0   \n",
       "3  2203838767            1         0   \n",
       "4  1948423238            1         0   \n",
       "\n",
       "                                         description  followers_count  \\\n",
       "0  A bot that creates hilarious joax via Google's...             1058   \n",
       "1                          Bot by @czircon. #botALLY               26   \n",
       "2  daily writing assignments, posts twice a day ~...              148   \n",
       "3  This is a bot to explain things like love and ...              116   \n",
       "4  It's lonely out in space. Rookie @exoriders pa...               51   \n",
       "\n",
       "   friends_count  listed_count  favourites_count  verified  statuses_count  \\\n",
       "0              1            83                 0     False            7678   \n",
       "1              0             6                 0     False            6832   \n",
       "2              0            22                 0     False            1723   \n",
       "3              0            39                 0     False            8421   \n",
       "4              0            19                 0     False           13622   \n",
       "\n",
       "   status default_profile  name  bot  \n",
       "0       1            None     1    0  \n",
       "1       1            None     1    0  \n",
       "2       1            None     1    0  \n",
       "3       1            None     1    0  \n",
       "4       1            None     1    0  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bot = df_bot.withColumn('bot',when(df_bot['bot'].isNull(),0).otherwise(df_bot['bot']))\n",
    "df_bot.limit(5) .toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do the same with the other booelan fields:\n",
    "    Run next commends:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_str</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>url</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>default_profile_image</th>\n",
       "      <th>has_extended_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3271095818</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>51</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>7/7/2015 15:23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2515</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>195370058</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>This is what I do. I drop truth bombs.</td>\n",
       "      <td>None</td>\n",
       "      <td>2925</td>\n",
       "      <td>3</td>\n",
       "      <td>139</td>\n",
       "      <td>9/26/2010 14:45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>708</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2980456333</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>8063</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>Tue Jan 13 19:42:21 +0000 2015</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>119</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.82E+17</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>I luv Zendya. She is SO awesome. Shak it up is...</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun Oct 02 04:54:18 +0000 2016</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>125</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2719936344</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>•_£•__•_¢•_´•__•_£•_ø•_¨•_¨•_´•_£•_«•_©•_¨•_¤‹...</td>\n",
       "      <td>None</td>\n",
       "      <td>5744</td>\n",
       "      <td>3</td>\n",
       "      <td>112</td>\n",
       "      <td>Sat Aug 09 19:09:46 +0000 2014</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>105941</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id_str  screen_name  location  \\\n",
       "0  3271095818            1         0   \n",
       "1   195370058            1         0   \n",
       "2  2980456333            1         0   \n",
       "3    7.82E+17            1         0   \n",
       "4  2719936344            1         1   \n",
       "\n",
       "                                         description   url  followers_count  \\\n",
       "0                                               None  None               51   \n",
       "1             This is what I do. I drop truth bombs.  None             2925   \n",
       "2                                               None  None             8063   \n",
       "3  I luv Zendya. She is SO awesome. Shak it up is...  None                2   \n",
       "4  •_£•__•_¢•_´•__•_£•_ø•_¨•_¨•_´•_£•_«•_©•_¨•_¤‹...  None             5744   \n",
       "\n",
       "   friends_count  listed_count                      created_at  \\\n",
       "0              3             9                  7/7/2015 15:23   \n",
       "1              3           139                 9/26/2010 14:45   \n",
       "2              1            13  Tue Jan 13 19:42:21 +0000 2015   \n",
       "3             51             0  Sun Oct 02 04:54:18 +0000 2016   \n",
       "4              3           112  Sat Aug 09 19:09:46 +0000 2014   \n",
       "\n",
       "   favourites_count  verified  statuses_count lang  status  default_profile  \\\n",
       "0                 0         0            2515   en       1                1   \n",
       "1                 0         0             708   en       1                0   \n",
       "2                42         0             119   en       1                0   \n",
       "3                75         0             125   en       1                0   \n",
       "4                 2         0          105941   en       1                0   \n",
       "\n",
       "  default_profile_image has_extended_profile  name  bot  \n",
       "0                 False                False     1  1.0  \n",
       "1                  None                 None     1  NaN  \n",
       "2                  None                 None     1  NaN  \n",
       "3                  None                 None     1  NaN  \n",
       "4                  None                 None     1  NaN  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bot = df_bot.withColumn('verified',df['verified'].cast(IntegerType()))\n",
    "df_bot = df_bot.withColumn('default_profile',df_bot['default_profile'].cast(IntegerType()))\n",
    "\n",
    "df_bot = df_bot.withColumn('verified',when(df_bot['verified'].isNull(),0).otherwise(df_bot['verified']))\n",
    "df_bot = df_bot.withColumn('default_profile',when(df_bot['default_profile'].isNull(),0).otherwise(df_bot['default_profile']))\n",
    "\n",
    "df_bot.limit(5) .toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many bots and none bots we have in the data?\n",
    "\n",
    "Run the next command to check out! "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "190"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bot.where(df['bot']==0).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "322"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bot.where(df['bot']==1).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Happy with the results? \n",
    "\n",
    "df = df_bot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ **Task :** \n",
    "\n",
    "#### drop N/A functionality - dropna()\n",
    "\n",
    "\n",
    "`dropna` functionality is dropping rows where the column given value is null.\n",
    "\n",
    "\n",
    "1. Drop `id_str` column completly\n",
    "2. Drop rows with N/A for `description`:\n",
    "\n",
    "Code example:\n",
    "```python\n",
    "df_new = df.drop('id_str')\n",
    "\n",
    "# in order to avoid errors, drop rows with null/None or N/A for description\n",
    "df_new = df_new.dropna(subset=['description'])\n",
    "# validate yourself\n",
    "df_new.count()\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = df.drop('id_str')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2433"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# in order to avoid errors, drop rows with null/None or N/A for description\n",
    "df_new = df_new.dropna(subset=['description'])\n",
    "# validate yourself\n",
    "df_new.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Happy with the results?\n",
    "\n",
    "df = df_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run the next commend, we will need it for chapter number 4\n",
    "sub = df.selectExpr('description','bot as label')\n",
    "sub.write.mode('overwrite').parquet(\"train_data_only_description\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Save updated DataFrame to file**\n",
    "To optimize, speed up queries, and maintain schema information, save the DataFrame as a parquet file. \n",
    "\n",
    ">Parquet is a columnar file format that provides optimizations to speed up queries and is a far more efficient file format than CSV or JSON, supported by many data processing systems\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "✅ **Task :** \n",
    "\n",
    "#### User Define Function - udf functionality\n",
    "\n",
    "`description` is the only string column left.\n",
    "Spark pattern mining algorithm takes an Array of unique Strings as in input.\n",
    "\n",
    "Hence, for executing pattern mining on description, you implement a function that takes description column string\n",
    "and turns it into an Array of unique Strings.\n",
    "\n",
    "For doing it, you will create a UDF - user define function.\n",
    "\n",
    "\n",
    "\n",
    "Code example for guidence:\n",
    "\n",
    "```python\n",
    "from pyspark.sql.types import ArrayType, StringType\n",
    "from pyspark.sql.functions import udf\n",
    "\n",
    "def split_and_set(some_str):\n",
    "    return {your python code goes here}\n",
    "\n",
    "# connect everything together: \n",
    "# set the udf\n",
    "list_udf = udf(lambda y: split_and_set(y), ArrayType(StringType()))\n",
    "\n",
    "# call udf from withColumn:\n",
    "new_df = df.withColumn('description', list_udf(df['description']))\n",
    "\n",
    "#validate yourself!\n",
    "new_df.take(2)\n",
    "\n",
    "#all good?\n",
    "df = new_df\n",
    "```\n",
    "\n",
    "\n",
    "You might get errors, in the exception log stack,\n",
    "search for `AnalysisException` and try to understand the problem.\n",
    "\n",
    "Try to think about what will happen if you run the code twice?\n",
    "\n",
    "Do it with a pointer to new DataFrame so you won't lose your results.\n",
    "\n",
    "\n",
    "<details><summary>Answer</summary>\n",
    "<p>\n",
    "\n",
    "```python\n",
    "from pyspark.sql.types import ArrayType, StringType\n",
    "from pyspark.sql.functions import udf\n",
    "\n",
    "def split_and_set(some_str):\n",
    "    if isinstance(some_str, str):\n",
    "        some_str = ''.join(c for c in some_str if c not in \"[](){}<>,'/.\")\n",
    "        return list(set(some_str.split(' ')))\n",
    "    return some_str\n",
    "\n",
    "\n",
    "list_udf = udf(lambda y: split_and_set(y), ArrayType(StringType()))\n",
    "\n",
    "new_df = df.withColumn('description', list_udf(df['description']))\n",
    "df = new_df\n",
    "```\n",
    "    \n",
    "</p>\n",
    "</details>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['csds', 'lola', 'b']"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check out this python code. Run it. What did you get?\n",
    "# will this work for the task?\n",
    "# how do you combine it with UDF?\n",
    "\n",
    "def split_and_set(some_str):\n",
    "    if isinstance(some_str, str):\n",
    "        some_str = ''.join(c for c in some_str if c not in \"[](){}<>,'/.\")\n",
    "        return list(set(some_str.split(' ')))\n",
    "    return some_str\n",
    "\n",
    "some_str = '[csds b lol,a]'\n",
    "\n",
    "split_and_set(split_and_set(some_str))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import ArrayType, StringType\n",
    "from pyspark.sql.functions import udf\n",
    "\n",
    "def split_and_set(some_str):\n",
    "    if isinstance(some_str, str):\n",
    "        some_str = ''.join(c for c in some_str if c not in \"[](){}<>,'/.\")\n",
    "        return list(set(some_str.split(' ')))\n",
    "    return some_str\n",
    "\n",
    "list_udf = udf(lambda y: split_and_set(y), ArrayType(StringType()))\n",
    "\n",
    "new_df = df.withColumn('description', list_udf(df['description']))\n",
    "df = new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "\n",
    "\n",
    "# Validate yourself and save to Parquet!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before saving the DataFrame to Parquet, look at a sample of the data to validate it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>screen_name</th>\n",
       "      <th>location</th>\n",
       "      <th>description</th>\n",
       "      <th>url</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>listed_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>favourites_count</th>\n",
       "      <th>verified</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>lang</th>\n",
       "      <th>status</th>\n",
       "      <th>default_profile</th>\n",
       "      <th>default_profile_image</th>\n",
       "      <th>has_extended_profile</th>\n",
       "      <th>name</th>\n",
       "      <th>bot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[I, This, what, drop, is, bombs, do, truth]</td>\n",
       "      <td>None</td>\n",
       "      <td>2925</td>\n",
       "      <td>3</td>\n",
       "      <td>139</td>\n",
       "      <td>9/26/2010 14:45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>708</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[Zendya, disney, zswager, I, awesome, Shak, my...</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun Oct 02 04:54:18 +0000 2016</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>125</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>[•_£•__•_¢•_´•__•_£•_ø•_¨•_¨•_´•_£•_«•_©•_¨•_¤...</td>\n",
       "      <td>None</td>\n",
       "      <td>5744</td>\n",
       "      <td>3</td>\n",
       "      <td>112</td>\n",
       "      <td>Sat Aug 09 19:09:46 +0000 2014</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>105941</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>[by, published, papers, Im, IFTTT, a, Powered,...</td>\n",
       "      <td>https://t.co/YE10egUojI</td>\n",
       "      <td>883</td>\n",
       "      <td>1</td>\n",
       "      <td>42</td>\n",
       "      <td>9/8/2014 18:51</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2574</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>[Country, Twitter, Official, American, Countdo...</td>\n",
       "      <td>http://t.co/a8Q3vDOdl3</td>\n",
       "      <td>26434</td>\n",
       "      <td>553</td>\n",
       "      <td>304</td>\n",
       "      <td>Sat Apr 11 01:10:17 +0000 2009</td>\n",
       "      <td>3786</td>\n",
       "      <td>0</td>\n",
       "      <td>7110</td>\n",
       "      <td>en</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   screen_name  location                                        description  \\\n",
       "0            1         0        [I, This, what, drop, is, bombs, do, truth]   \n",
       "1            1         0  [Zendya, disney, zswager, I, awesome, Shak, my...   \n",
       "2            1         1  [•_£•__•_¢•_´•__•_£•_ø•_¨•_¨•_´•_£•_«•_©•_¨•_¤...   \n",
       "3            1         1  [by, published, papers, Im, IFTTT, a, Powered,...   \n",
       "4            1         1  [Country, Twitter, Official, American, Countdo...   \n",
       "\n",
       "                       url  followers_count  friends_count  listed_count  \\\n",
       "0                     None             2925              3           139   \n",
       "1                     None                2             51             0   \n",
       "2                     None             5744              3           112   \n",
       "3  https://t.co/YE10egUojI              883              1            42   \n",
       "4   http://t.co/a8Q3vDOdl3            26434            553           304   \n",
       "\n",
       "                       created_at  favourites_count  verified  statuses_count  \\\n",
       "0                 9/26/2010 14:45                 0         0             708   \n",
       "1  Sun Oct 02 04:54:18 +0000 2016                75         0             125   \n",
       "2  Sat Aug 09 19:09:46 +0000 2014                 2         0          105941   \n",
       "3                  9/8/2014 18:51                 0         0            2574   \n",
       "4  Sat Apr 11 01:10:17 +0000 2009              3786         0            7110   \n",
       "\n",
       "  lang  status  default_profile default_profile_image has_extended_profile  \\\n",
       "0   en       1                0                  None                 None   \n",
       "1   en       1                0                  None                 None   \n",
       "2   en       1                0                  None                 None   \n",
       "3   en       1                1                 False                False   \n",
       "4   en       1                0                  None                 None   \n",
       "\n",
       "   name  bot  \n",
       "0     1  NaN  \n",
       "1     1  NaN  \n",
       "2     1  NaN  \n",
       "3     1  1.0  \n",
       "4     1  NaN  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.limit(5) .toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Happy with the results? Save updated DataFrame to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# happy with the results? write to file!\n",
    "# run this command\n",
    "df.write.mode('overwrite').parquet(\"final_train_data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checkout `final_train_data` libaray on Jupyter Files\n",
    "\n",
    "What file format did you get? **write** it in our chat! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Well Done! 👏👏👏\n",
    "\n",
    "\n",
    "## You just finished:  Intro to Data Cleaning and Preparation \n",
    "\n",
    "\n",
    "## Next Chapter:  Apache Spark ML - Create train and test set "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
